const Database = require('better-sqlite3');
const path = require('path');

const CURRENT_DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const BACKUP_DB_PATH = path.join(process.cwd(), 'data', 'delivery-management-backup-20250706_153134.db');

console.log('🔄 撤销错误恢复并从正确备份文件恢复实例表');
console.log('=============================================');

try {
  // 1. 首先检查备份文件是否存在
  const fs = require('fs');
  if (!fs.existsSync(BACKUP_DB_PATH)) {
    console.log('❌ 备份文件不存在:', BACKUP_DB_PATH);
    return;
  }
  
  console.log('✅ 找到正确的备份文件:', BACKUP_DB_PATH);
  
  // 2. 连接当前数据库，删除刚才错误恢复的表
  const currentDb = new Database(CURRENT_DB_PATH);
  
  console.log('\\n🗑️ 撤销刚才的错误恢复...');
  
  // 删除刚才创建的错误表
  const wrongTables = [
    'device_instances_L4_06_01_01_01_000',
    'device_instances_配管_0047', 
    'device_instances_配管_0015'
  ];
  
  for (const tableName of wrongTables) {
    try {
      currentDb.prepare(`DROP TABLE IF EXISTS [${tableName}]`).run();
      console.log(`  ✅ 删除错误表: ${tableName}`);
    } catch (error) {
      console.log(`  ⚠️  删除 ${tableName} 失败: ${error.message}`);
    }
  }
  
  // 3. 连接备份数据库
  const backupDb = new Database(BACKUP_DB_PATH);
  
  console.log('\\n🔍 分析备份数据库中的实例表...');
  
  // 查找备份中的所有实例表
  const backupInstanceTables = backupDb.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📊 备份中找到 ${backupInstanceTables.length} 个实例表:`);
  backupInstanceTables.forEach(table => {
    const count = backupDb.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
    console.log(`  📋 ${table.name}: ${count.count} 条记录`);
  });
  
  // 4. 恢复每个实例表
  console.log('\\n🔄 开始从备份恢复实例表...');
  
  let restoredTables = 0;
  let restoredRecords = 0;
  
  for (const table of backupInstanceTables) {
    console.log(`\\n📋 恢复表: ${table.name}`);
    
    try {
      // 获取表结构
      const tableSchema = backupDb.prepare(`
        SELECT sql FROM sqlite_master 
        WHERE type='table' AND name = ?
      `).get(table.name);
      
      if (!tableSchema) {
        console.log(`  ❌ 无法获取表结构`);
        continue;
      }
      
      // 在当前数据库中创建表
      currentDb.prepare(tableSchema.sql).run();
      console.log(`  ✅ 创建表结构`);
      
      // 获取所有数据
      const tableData = backupDb.prepare(`SELECT * FROM [${table.name}]`).all();
      
      if (tableData.length > 0) {
        // 获取列名
        const columns = backupDb.prepare(`PRAGMA table_info([${table.name}])`).all();
        const columnNames = columns.map(col => col.name);
        const placeholders = columnNames.map(() => '?').join(', ');
        const columnList = columnNames.map(name => `[${name}]`).join(', ');
        
        // 批量插入数据
        const insertStmt = currentDb.prepare(`
          INSERT INTO [${table.name}] (${columnList}) 
          VALUES (${placeholders})
        `);
        
        let insertedCount = 0;
        for (const row of tableData) {
          try {
            const values = columnNames.map(col => row[col]);
            insertStmt.run(...values);
            insertedCount++;
          } catch (insertError) {
            if (!insertError.message.includes('UNIQUE constraint failed')) {
              console.log(`    ⚠️  插入数据失败: ${insertError.message}`);
            }
          }
        }
        
        console.log(`  ✅ 插入 ${insertedCount}/${tableData.length} 条记录`);
        restoredRecords += insertedCount;
      } else {
        console.log(`  ℹ️  表为空，只恢复结构`);
      }
      
      restoredTables++;
      
    } catch (error) {
      if (error.message.includes('already exists')) {
        console.log(`  ℹ️  表已存在，跳过`);
      } else {
        console.log(`  ❌ 恢复失败: ${error.message}`);
      }
    }
  }
  
  // 5. 同样恢复参数表
  console.log('\\n🔍 恢复参数表...');
  
  const backupParamTables = backupDb.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'instance_params_%'
    ORDER BY name
  `).all();
  
  console.log(`📊 备份中找到 ${backupParamTables.length} 个参数表`);
  
  for (const table of backupParamTables) {
    try {
      // 获取表结构
      const tableSchema = backupDb.prepare(`
        SELECT sql FROM sqlite_master 
        WHERE type='table' AND name = ?
      `).get(table.name);
      
      if (tableSchema) {
        // 在当前数据库中创建表
        currentDb.prepare(tableSchema.sql).run();
        
        // 获取并插入数据
        const tableData = backupDb.prepare(`SELECT * FROM [${table.name}]`).all();
        
        if (tableData.length > 0) {
          const columns = backupDb.prepare(`PRAGMA table_info([${table.name}])`).all();
          const columnNames = columns.map(col => col.name);
          const placeholders = columnNames.map(() => '?').join(', ');
          const columnList = columnNames.map(name => `[${name}]`).join(', ');
          
          const insertStmt = currentDb.prepare(`
            INSERT INTO [${table.name}] (${columnList}) 
            VALUES (${placeholders})
          `);
          
          let insertedCount = 0;
          for (const row of tableData) {
            try {
              const values = columnNames.map(col => row[col]);
              insertStmt.run(...values);
              insertedCount++;
            } catch (insertError) {
              if (!insertError.message.includes('UNIQUE constraint failed')) {
                console.log(`    ⚠️  插入参数数据失败: ${insertError.message}`);
              }
            }
          }
          
          console.log(`  ✅ ${table.name}: ${insertedCount} 条参数记录`);
          restoredRecords += insertedCount;
        }
      }
    } catch (error) {
      if (!error.message.includes('already exists')) {
        console.log(`  ❌ 恢复参数表 ${table.name} 失败: ${error.message}`);
      }
    }
  }
  
  // 6. 最终验证
  console.log('\\n📊 恢复完成，最终验证');
  console.log('========================');
  
  const finalInstanceTables = currentDb.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND (name LIKE 'device_instances_%' OR name LIKE 'instance_params_%')
    ORDER BY name
  `).all();
  
  let finalInstances = 0;
  let finalParams = 0;
  
  for (const table of finalInstanceTables) {
    const count = currentDb.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
    
    if (table.name.startsWith('device_instances_')) {
      finalInstances += count.count;
      if (count.count > 0) {
        console.log(`  📊 ${table.name}: ${count.count} 个实例`);
      }
    } else if (table.name.startsWith('instance_params_')) {
      finalParams += count.count;
      if (count.count > 0) {
        console.log(`  📊 ${table.name}: ${count.count} 条参数`);
      }
    }
  }
  
  console.log('\\n🎉 恢复汇总:');
  console.log(`✅ 恢复实例表数量: ${restoredTables} 个`);
  console.log(`📊 恢复数据记录: ${restoredRecords} 条`);
  console.log(`📋 设备实例总数: ${finalInstances} 个`);
  console.log(`📋 参数记录总数: ${finalParams} 条`);
  
  if (finalInstances > 0) {
    console.log('\\n🎉 成功从正确的备份文件恢复您的实例数据！');
    console.log('抱歉之前的错误操作，现在数据已从正确的备份文件恢复。');
  } else {
    console.log('\\n⚠️  备份文件中可能没有实例数据，或恢复过程遇到问题');
  }
  
  currentDb.close();
  backupDb.close();
  
} catch (error) {
  console.error('❌ 恢复失败:', error.message);
  console.error(error.stack);
}